﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DataHelper;
namespace DAL
{
    public class DefDicDao
    {
        public bool ExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSql(strSQL);
        }

        public DataTable GetOneInfo(string tableName, string id)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from " + tableName + "  Where ID=" + id);
        }
        public DataTable GetOneInfo(string tableName, string name, string statu)
        { return SQLHelper.ExecuteSqlGetDrToDt("Select * from " + tableName + "  Where [Name]='" + name + "' and Statu='" + statu + "'"); }
        public DataTable GetInItLists(string tableName)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select ID,Name From " + tableName + " Where Statu='Y'  order by AbsIndex");
        }
        /// <summary>
        /// 根据SQL语句，获取字典列表
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        //public DataTable GetLists(string strSQL)
        //{
        //    return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        //}
        public DataTable GetLists(string tableName, string statu)
        {
            string strSQL = "";
            if (statu != "")
                strSQL = "select  * from " + tableName + " Where Statu='" + statu + "' order by  AbsIndex";
            else
                strSQL = "select  * from " + tableName + "   order by  AbsIndex";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //查询
        public DataTable GetLists(string tableName, string serWord, string statu)
        {
            string strSQL = "select  * from " + tableName + " Where [Name] like '%" + serWord + "%' order by  AbsIndex";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //
        public DataTable GetLists(string tableName, string key, bool isFull)
        {
            string strSQL = string.Empty;
            if (isFull)
                strSQL = "select top 50 [Name] from " + tableName + " Where  Statu='Y' order by  AbsIndex";
            else
                strSQL = "select top 50 [Name] from " + tableName + " Where  [Name] like '%" + key + "%' and  Statu='Y' order by  AbsIndex";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //获取符合条件的行数
        public string GetNumber(string tableName, string fieldName,string fieldValue)
        {
            return SQLHelper.ExecuteSqlGetNumber("Select count(*) from " + tableName + "  Where " + fieldName + "='" + fieldValue + "'");
        }
        //获取指定表的所有的行数
        public string GetNumber(string tableName,string dateType)
        {
            string sql="Select count(*) from " + tableName + " Where ";
            switch (dateType)
            {
                case "dd"://日
                    sql+=" datediff(day,InputTime,'" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')=0 ";
                    break;
                case "mm"://月
                    sql += " datediff(month,InputTime,'" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')=0 ";
                    break;
                case "yy"://年
                    sql += " datediff(year,InputTime,'" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')=0 ";
                    break;
                case "qq"://季度
                    sql += " datediff(quarter,InputTime,'" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')=0 ";
                    break;
                case "ww"://周
                    sql += " datediff(week,InputTime,'" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')=0 ";
                    break;
                default:
                    sql = "Select count(*) from " + tableName;
                    break;
            }

            return SQLHelper.ExecuteSqlGetNumber(sql);
         
        }
        //获取所有数据列表
        public DataTable GetItems(string tableName,string fields)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select " + fields + " From " + tableName);

        }
        //操作Wage_Option 字典
        public string GetOptionValue(string key)
        {
            return SQLHelper.ExecuteSqlGetNumber("select memo from Wage_Option where [Name]='" + key + "'");
        }
        public bool UpdateOptionValue(string key, string value)
        {
            return SQLHelper.ExecuteSql("update Wage_Option set Memo='" + value + "' where [Name]='" + key + "'");
        }
    }
}
